package com.admin.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

import com.admin.model.Gw;
import com.admin.model.Gwckrz;
import com.admin.model.Gwczrz;
import com.admin.model.JieDian;
import com.admin.model.Menu;
import com.admin.model.Ptczrz;
import com.admin.model.User;
import com.admin.model.Wjcxrz;
import com.admin.model.Wjxzrz;

@Service("CommonDao")
public class CommonDao extends JdbcDaoSupport{
	 public static String getUUID() {  
	        return UUID.randomUUID().toString().replace("-", "");  
	    } 
	
	@Resource
	public void setJb(JdbcTemplate jdbctemplate) {
		super.setJdbcTemplate(jdbctemplate);
	}

	public boolean login(User user) {
		if(this.getJdbcTemplate().queryForInt("select count(*) from admin_user where username ='"+user.getUsername()+"' and password = '"+user.getPassword()+"'")>0){
			return true;
		}
		return false;
	}

	public void updatePassword(User user) {
		this.getJdbcTemplate().execute("update admin_user set password = '"+user.getNewpassword()+"' where username = '"+user.getUsername()+"'");	
	}

	public int getUserTotal(String username) {
		String sql="select count(*) from admin_user";
		if(username!=null&&!username.equals("")){
			sql+=" where username like '%"+username+"%'";
		}
		return this.getJdbcTemplate().queryForInt(sql);
	}

	public List<User> selectByPage(String page,String username) {
		List<User> users=new ArrayList<User>();
		List<Map<String, Object>> list;
		if(username!=null&&!username.equals("")){
		list=this.getJdbcTemplate().queryForList("select * from admin_user where username like '%"+username+"%' order by createTime desc limit "+(Integer.valueOf(page)-1)*10+",10");
		}else{
		list=this.getJdbcTemplate().queryForList("select * from admin_user order by createTime desc limit "+(Integer.valueOf(page)-1)*10+",10");
		}
		for(Map<String,Object> map:list){
			User u=new User();
			u.setId((String)map.get("id"));
			u.setUsername((String)map.get("username"));
			u.setZsxm((String)map.get("zsxm"));
			u.setSjhm((String)map.get("sjhm"));
			u.setSzdw((String)map.get("szdw"));
			u.setZz((String)map.get("zz"));
			users.add(u);
		}
		return users;
	}

	public void saveUser(User user) {
		SimpleDateFormat sd =new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		this.getJdbcTemplate().execute("insert into admin_user(id,username,password,zsxm,sjhm,szdw,zz,createTime) "
				+ "values('"+getUUID()+"','"+user.getUsername()+"','"+user.getPassword()+"','"+user.getZsxm()+"','"+user.getSjhm()+"','"+user.getSzdw()+"','"+user.getZz()+"','"+sd.format(new Date())+"')");
	}

	public User getUserById(String userid) {
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_user where id = '"+userid+"'");
		Map<String,Object> map=list.get(0);
		User u=new User();
		u.setId((String)map.get("id"));
		u.setUsername((String)map.get("username"));
		u.setPassword((String)map.get("password"));
		u.setZsxm((String)map.get("zsxm"));
		u.setSjhm((String)map.get("sjhm"));
		u.setSzdw((String)map.get("szdw"));
		u.setZz((String)map.get("zz"));
		return u;
	}

	public void updateUserById(User user) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		this.getJdbcTemplate().execute("update admin_user set username = '"+user.getUsername()
		+"',zsxm = '"+user.getZsxm()+"',sjhm = '"+user.getSjhm()+"',szdw = '"+user.getSzdw()+"',zz = '"+user.getZz()
		+"',createTime = '"+df.format(new Date())+"' where id = '"+user.getId()+"'");
	}

	public void deleteUserById(String userid) {
		this.getJdbcTemplate().execute("delete from admin_user where id = '"+userid+"'");
		
	}

	public int getCountPtczrz() {
		
		return this.getJdbcTemplate().queryForInt("select count(*) from admin_ptczrz");
	}

	public List<Ptczrz> findByPage(String page) {
		List<Ptczrz> users=new ArrayList<Ptczrz>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_ptczrz limit "+(Integer.valueOf(page)-1)*10+",10");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(Map<String,Object> map:list){
			Ptczrz u=new Ptczrz();
			u.setYhm((String)map.get("yhm"));
			u.setCzmc((String)map.get("czmc"));
			u.setCzsj(df.format(map.get("czsj")));
			users.add(u);
		}
		return users;
	}

	public int getCountGwczrz() {
		return this.getJdbcTemplate().queryForInt("select count(*) from admin_gwczrz");
	}

	public List<Gwczrz> findByPageGwczrz(String page) {
		List<Gwczrz> users=new ArrayList<Gwczrz>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_gwczrz limit "+(Integer.valueOf(page)-1)*10+",10");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(Map<String,Object> map:list){
			Gwczrz u=new Gwczrz();
			u.setYhm((String)map.get("yhm"));
			u.setCzlx((String)map.get("czlx"));
			u.setGwid(((String)map.get("gwid")).replaceAll("f", "w"));
			u.setCzsj(df.format(map.get("czsj")));
			users.add(u);
		}
		return users;
	}

	public int getCountGwckrz() {
		return this.getJdbcTemplate().queryForInt("select count(*) from admin_gwckrz");
	}

	public List<Gwckrz> findByPageGwckrz(String page) {
		List<Gwckrz> users=new ArrayList<Gwckrz>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_gwckrz limit "+(Integer.valueOf(page)-1)*10+",10");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(Map<String,Object> map:list){
			Gwckrz u=new Gwckrz();
			u.setYhm((String)map.get("yhm"));
			u.setFwurl((String)map.get("fwurl"));
			u.setFwsj(df.format(map.get("fwsj")));
			users.add(u);
		}
		return users;
	}

	public int getCountGwcx() {
		return this.getJdbcTemplate().queryForInt("select count(*) from common_gw");
	}

	public List<Gw> findGwByPage(String page) {
		List<Gw> users=new ArrayList<Gw>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from common_gw limit "+(Integer.valueOf(page)-1)*10+",10");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		for(Map<String,Object> map:list){
			Gw u=new Gw();
			u.setGwbt((String)map.get("title"));
			u.setId(String.valueOf(map.get("id")));
			u.setGkzrbm((String)map.get("dept"));
			u.setFbr((String)map.get("account"));
			u.setFbsj(df.format(map.get("pub_time")));
			u.setContent((String)map.get("content_text"));
			if(map.get("title")==null){
				u.setGwbt("");
			}
			if(map.get("dept")==null){
				u.setGkzrbm("");
			}
			if(map.get("account")==null){
				u.setFbr("");
			}
			if(map.get("content_text")==null){
				u.setContent("");
			}
			users.add(u);
		}
		return users;
	}

	public String findGwById(String gwid) {
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from common_gw where id = '"+gwid+"'");
		Map<String,Object> map=list.get(0);
		return (String)map.get("content_text");
	}

	public List<String> getQx(String userid) {
		List<String> sList=new ArrayList<String>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_user where id = '"+userid+"'");
		Map<String,Object> map=list.get(0);
		String qx=(String)map.get("qx");
		if(qx!=null&&!qx.equals("")&&!qx.equalsIgnoreCase("null")){
			sList=Arrays.asList(qx.split(","));
		}
		return sList;
	}

	public void saveQx(String userid, String qx) {
		this.getJdbcTemplate().execute("update admin_user set qx = '"+qx+"' where id = '"+userid+"'");
	}

	public String findQxByUsername(String username) {
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_user where username = '"+username+"'");
		Map<String,Object> map=list.get(0);
		return (String)map.get("qx");
	}

	public void findMeneById(List<Menu> menuList, String qx) {
     String [] strArr=qx.split(",");
     for(int i=0;i<strArr.length;i++){
    	 List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_qx where id = '"+strArr[i]+"'");
 		Map<String,Object> map=list.get(0); 
 		Menu m=new Menu();
 		m.setId((String)map.get("id"));
 		m.setFid((String)map.get("fid"));
 		m.setMenuName((String)map.get("ml"));
 		m.setPx(String.valueOf(map.get("px")));
 		m.setUrl((String)map.get("url"));
 		menuList.add(m);
     }
	}

	public int getCountWjxzrz() {
		return this.getJdbcTemplate().queryForInt("select count(*) from admin_wjxrrz");
	}

	public List<Wjxzrz> findByPageWjxzrz(String page) {
		List<Wjxzrz> users=new ArrayList<Wjxzrz>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_wjxrrz limit "+(Integer.valueOf(page)-1)*10+",10");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		for(Map<String,Object> map:list){
			Wjxzrz u=new Wjxzrz();
			u.setId((String)map.get("id"));
			u.setWjhs((String)map.get("wjhs"));
			u.setTjr((String)map.get("tjr"));
			u.setTjsj(df.format(map.get("tjsj")));
			users.add(u);
		}
		return users;
	}

	public int getCountWjcxrz() {
		return this.getJdbcTemplate().queryForInt("select count(*) from admin_wjcxrz");
	}

	public List<Wjcxrz> findByPageWjcxrz(String page) {
		List<Wjcxrz> users=new ArrayList<Wjcxrz>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_wjcxrz limit "+(Integer.valueOf(page)-1)*10+",10");
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		for(Map<String,Object> map:list){
			Wjcxrz u=new Wjcxrz();
			u.setId((String)map.get("id"));
			u.setCkr((String)map.get("ckr"));
			u.setWh((String)map.get("wh"));
			u.setWjhs((String)map.get("wjhs"));
			u.setTime(df.format(map.get("time")));
			users.add(u);
		}
		return users;
	}

	public List<JieDian> getJd() {
		List<JieDian> users=new ArrayList<JieDian>();
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_jiedian");
		for(Map<String,Object> map:list){
			JieDian u=new JieDian();
			u.setId((String)map.get("id"));
			u.setName((String)map.get("name"));
			u.setIp((String)map.get("ip"));
			users.add(u);
		}
		return users;
	}

	public void setJcData(String jdip, Map<String, List<String>> map) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_cpujc where ip = '"+jdip+"' order by time desc limit 0,100");
		List<Map<String, Object>> list1=this.getJdbcTemplate().queryForList("select * from admin_ncjc where ip = '"+jdip+"' order by time desc limit 0,100");
		List<Map<String, Object>> list2=this.getJdbcTemplate().queryForList("select * from admin_ypjc where ip = '"+jdip+"' order by time desc limit 0,100");
		List<String> data1=new ArrayList<String>();
		List<String> data2=new ArrayList<String>();
		List<String> data3=new ArrayList<String>();
		List<String> data4=new ArrayList<String>();
		List<String> data5=new ArrayList<String>();
		List<String> data6=new ArrayList<String>();
		for(int i=list.size()-1;i>=0;i--){
			data1.add(df.format(list.get(i).get("time")));
			data2.add((String)list.get(i).get("dqz"));
		}
		for(int i=list1.size()-1;i>=0;i--){
			data3.add(df.format(list1.get(i).get("time")));
			data4.add((String)list1.get(i).get("dqz"));
		}
		for(int i=list2.size()-1;i>=0;i--){
			data5.add(df.format(list2.get(i).get("time")));
			data6.add((String)list2.get(i).get("dqz"));
		}
		map.put("data1", data1);
		map.put("data2", data2);
		map.put("data3", data3);
		map.put("data4", data4);
		map.put("data5", data5);
		map.put("data6", data6);
	}

	public void setJcData(String jdip, String startTime, String endTime, Map<String, List<String>> map) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList("select * from admin_cpujc where ip = '"+jdip+"' and time > '"+startTime+"' and time < '"+endTime+"' order by time desc");
		List<Map<String, Object>> list1=this.getJdbcTemplate().queryForList("select * from admin_ncjc where ip = '"+jdip+"' and time > '"+startTime+"' and time < '"+endTime+"' order by time desc");
		List<Map<String, Object>> list2=this.getJdbcTemplate().queryForList("select * from admin_ypjc where ip = '"+jdip+"' and time > '"+startTime+"' and time < '"+endTime+"' order by time desc");
		List<String> data1=new ArrayList<String>();
		List<String> data2=new ArrayList<String>();
		List<String> data3=new ArrayList<String>();
		List<String> data4=new ArrayList<String>();
		List<String> data5=new ArrayList<String>();
		List<String> data6=new ArrayList<String>();
		for(int i=list.size()-1;i>=0;i--){
			data1.add(df.format(list.get(i).get("time")));
			data2.add((String)list.get(i).get("dqz"));
		}
		for(int i=list1.size()-1;i>=0;i--){
			data3.add(df.format(list1.get(i).get("time")));
			data4.add((String)list1.get(i).get("dqz"));
		}
		for(int i=list2.size()-1;i>=0;i--){
			data5.add(df.format(list2.get(i).get("time")));
			data6.add((String)list2.get(i).get("dqz"));
		}
		map.put("data1", data1);
		map.put("data2", data2);
		map.put("data3", data3);
		map.put("data4", data4);
		map.put("data5", data5);
		map.put("data6", data6);
	}

	public String getWjxzOrCxZl(String flag) {
		String sql;
		if(flag.equals("xz")){
			sql="select count(*) from admin_wjxrrz";
		}else{
			sql="select count(*) from admin_wjcxrz";
		}
		return String.valueOf(this.getJdbcTemplate().queryForInt(sql));
	}

	public void setWjxzOrWjcx(String flag, String startTime, String endTime, Map<String, Object> map) {
		String sql;
		String sql1;
		if(flag.equals("xz")){
			sql="select count(tjsj) num,tjsj from admin_wjxrrz where tjsj > '"+startTime+"' and tjsj < '"+endTime+"' group by tjsj order by tjsj asc";
			sql1="select count(*)  from admin_wjxrrz where tjsj > '"+startTime+"' and tjsj < '"+endTime+"'";
		}else{
			sql="select count(time) num,time tjsj from admin_wjcxrz where time > '"+startTime+"' and time < '"+endTime+"' group by time order by time asc";
			sql1="select count(*)  from admin_wjcxrz where time > '"+startTime+"' and time < '"+endTime+"'";
		}
		List<Map<String, Object>> list=this.getJdbcTemplate().queryForList(sql);
		List<String> data1=new ArrayList<String>();
		List<String> data2=new ArrayList<String>();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		for(Map<String,Object> m:list){
			data1.add(String.valueOf(m.get("num")));
			data2.add(df.format(m.get("tjsj")));
		}
	    map.put("total", String.valueOf(this.getJdbcTemplate().queryForInt(sql1)));
		map.put("data1", data1);
		map.put("data2", data2);
	}
	
}
